#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
考试项目课室管理系统数据库初始化脚本
Initialize_database.py - 创建和初始化完整数据库表结构
表结构优化版本 - 统一使用examination_rooms表，消除rooms表冗余
增强版本 - 添加课室管理功能字段和违规管理系统
"""

import pymysql
import os
import sys
import json
import re
from datetime import datetime

# 数据库配置 - 用户指定的配置信息
DB_CONFIG = {
    'host': os.getenv('DB_HOST', 'r4s.gwongzaak.top'),
    'user': os.getenv('DB_USER', 'root'),
    'password': os.getenv('DB_PASSWORD', 'A!3536651712a'),
    'database': os.getenv('DB_NAME', 'OA'),
    'charset': 'utf8mb4'
}


def get_db_connection():
    """获取数据库连接"""
    try:
        connection = pymysql.connect(**DB_CONFIG)
        print(f"✅ 数据库连接成功: {DB_CONFIG['host']}/{DB_CONFIG['database']}")
        return connection
    except Exception as e:
        print(f"❌ 数据库连接失败: {e}")
        return None


def create_staff_table(cursor):
    """创建员工信息表（主表）- 精确匹配index.php要求"""
    sql = """
    CREATE TABLE IF NOT EXISTS staff (
        user_id VARCHAR(50) PRIMARY KEY COMMENT '员工工号',
        name VARCHAR(100) NOT NULL COMMENT '姓名',
        gender ENUM('男', '女', '未知') DEFAULT '未知' COMMENT '性别',
        position VARCHAR(100) DEFAULT '未知' COMMENT '职位',
        dept_id VARCHAR(50) DEFAULT '0' COMMENT '部门ID',
        online_status VARCHAR(10) DEFAULT 'false' COMMENT '在线状态',
        serving BOOLEAN DEFAULT TRUE COMMENT '是否在职',
        latest_login TIMESTAMP NULL COMMENT '最后登录时间',
        service_years DECIMAL(4,1) DEFAULT 0.0 COMMENT '入职年限',
        responsibility INT DEFAULT 5 COMMENT '责任心等级(1-10)',
        risk_index DECIMAL(5,2) DEFAULT 1.00 COMMENT '风险指数',
        classroom_count INT DEFAULT 0 COMMENT '教室监考次数',
        lab_count INT DEFAULT 0 COMMENT '机房监考次数',
        id_number VARCHAR(18) COMMENT '身份证号',
        password_hash VARCHAR(255) COMMENT '密码哈希',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

        INDEX idx_name (name),
        INDEX idx_dept (dept_id),
        INDEX idx_serving (serving),
        INDEX idx_online (online_status),
        INDEX idx_risk (risk_index)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='员工信息表'
    """

    try:
        cursor.execute(sql)
        print("✅ 员工信息表(staff)创建成功")
        return True
    except Exception as e:
        print(f"❌ 员工信息表创建失败: {e}")
        return False


def create_user_roles_table(cursor):
    """创建用户角色表（无外键约束）"""
    sql = """
    CREATE TABLE IF NOT EXISTS user_roles (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id VARCHAR(50) NOT NULL COMMENT '用户ID',
        role ENUM('系统管理员', '考试项目管理员', '人事管理员', '普通账号') NOT NULL COMMENT '角色',
        assigned_by VARCHAR(50) COMMENT '分配者',
        assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '分配时间',

        UNIQUE KEY unique_user_role (user_id, role),
        INDEX idx_user_id (user_id),
        INDEX idx_role (role)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户角色表'
    """

    try:
        cursor.execute(sql)
        print("✅ 用户角色表(user_roles)创建成功")
        return True
    except Exception as e:
        print(f"❌ 用户角色表创建失败: {e}")
        return False


def create_login_attempts_table(cursor):
    """创建登录尝试表（精确匹配index.php要求）"""
    sql = """
    CREATE TABLE IF NOT EXISTS login_attempts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        ip_address VARCHAR(45) NOT NULL,
        username VARCHAR(50),
        attempt_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        success BOOLEAN DEFAULT FALSE,
        INDEX idx_ip_time (ip_address, attempt_time)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='登录尝试表'
    """

    try:
        cursor.execute(sql)
        print("✅ 登录尝试表(login_attempts)创建成功")
        return True
    except Exception as e:
        print(f"❌ 登录尝试表创建失败: {e}")
        return False


def create_blocked_ips_table(cursor):
    """创建IP封禁表（精确匹配index.php要求）"""
    sql = """
    CREATE TABLE IF NOT EXISTS blocked_ips (
        id INT AUTO_INCREMENT PRIMARY KEY,
        ip_address VARCHAR(45) UNIQUE NOT NULL,
        blocked_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        attempts_count INT DEFAULT 0
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='IP封禁表'
    """

    try:
        cursor.execute(sql)
        print("✅ IP封禁表(blocked_ips)创建成功")
        return True
    except Exception as e:
        print(f"❌ IP封禁表创建失败: {e}")
        return False


def create_examination_rooms_table(cursor):
    """创建统一的examination_rooms表（增强版本 - 支持课室管理功能）"""
    sql = """
    CREATE TABLE IF NOT EXISTS examination_rooms (
        id INT AUTO_INCREMENT PRIMARY KEY,
        exam_id INT NOT NULL DEFAULT 1 COMMENT '考试项目ID',

        -- 核心课室信息
        room_number VARCHAR(50) NOT NULL COMMENT '课室号',
        room_type VARCHAR(50) DEFAULT NULL COMMENT '课室类型',
        capacity INT DEFAULT 0 COMMENT '课室容量',

        -- 位置信息 - 支持双格式以兼容两个PHP文件
        building VARCHAR(20) DEFAULT NULL COMMENT '楼栋(文本格式，如"1号楼")',
        floor VARCHAR(20) DEFAULT NULL COMMENT '楼层(文本格式，如"3楼")',
        sub_building VARCHAR(10) DEFAULT NULL COMMENT '楼栋子编号(A/B座或主楼/辅楼)',
        building_number INT DEFAULT NULL COMMENT '楼栋号(数字格式，如1)',
        floor_number INT DEFAULT NULL COMMENT '楼层号(数字格式，如3)',

        -- 管理信息字段 - 增强版本
        administrator_name VARCHAR(100) DEFAULT '待填写' COMMENT '班主任姓名/课室管理员姓名',
        address VARCHAR(200) DEFAULT NULL COMMENT '课室详细地址(自动生成：楼栋+楼层+课室号)',
        purpose ENUM('考场','备用考场','考点办公室','接待室','广播室','医疗室','教师办公室','空白') DEFAULT '考场' COMMENT '课室用途',
        ownership ENUM('华材','技工') DEFAULT '华材' COMMENT '课室归属权',

        -- 系统管理信息
        created_by VARCHAR(50) DEFAULT NULL COMMENT '创建人',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

        -- 索引优化
        UNIQUE KEY uk_exam_room_number (exam_id, room_number),
        INDEX idx_room_number (room_number),
        INDEX idx_exam_id (exam_id),
        INDEX idx_building_floor_text (building, floor),
        INDEX idx_building_floor_num (building_number, floor_number),
        INDEX idx_room_type (room_type),
        INDEX idx_purpose (purpose),
        INDEX idx_ownership (ownership),
        INDEX idx_administrator (administrator_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='统一课室信息表(增强版本)'
    """

    try:
        cursor.execute(sql)
        print("✅ 统一课室信息表(examination_rooms)创建成功 - 增强版本")
        return True
    except Exception as e:
        print(f"❌ 统一课室信息表创建失败: {e}")
        return False


def create_exam_classrooms_table(cursor):
    """创建考试课室关联表（保持兼容性）"""
    sql = """
    CREATE TABLE IF NOT EXISTS exam_classrooms (
        id INT AUTO_INCREMENT PRIMARY KEY,
        exam_id INT NOT NULL DEFAULT 1 COMMENT '考试项目ID',
        classroom_number VARCHAR(50) NOT NULL COMMENT '课室号',
        classroom_type VARCHAR(50) DEFAULT NULL COMMENT '课室类型',
        building VARCHAR(20) DEFAULT NULL COMMENT '楼栋',
        floor VARCHAR(20) DEFAULT NULL COMMENT '楼层',
        sub_building VARCHAR(10) DEFAULT NULL COMMENT '楼栋子编号',
        floor_number INT DEFAULT NULL COMMENT '楼层序号',
        capacity INT DEFAULT 0 COMMENT '课室容量',
        notes TEXT COMMENT '备注信息',
        status ENUM('active', 'inactive') DEFAULT 'active' COMMENT '状态',
        created_by VARCHAR(50) DEFAULT NULL COMMENT '创建人',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

        UNIQUE KEY uk_exam_classroom (exam_id, classroom_number),
        INDEX idx_exam_id (exam_id),
        INDEX idx_classroom_number (classroom_number),
        INDEX idx_building_floor (building, floor),
        INDEX idx_classroom_type (classroom_type)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='考试课室关联表'
    """

    try:
        cursor.execute(sql)
        print("✅ 考试课室关联表(exam_classrooms)创建成功")
        return True
    except Exception as e:
        print(f"❌ 考试课室关联表创建失败: {e}")
        return False


def create_exams_table(cursor):
    """创建考试项目表（修复字段名以兼容exam_projects.php）"""
    sql = """
    CREATE TABLE IF NOT EXISTS exams (
        id INT AUTO_INCREMENT PRIMARY KEY,
        exam_name VARCHAR(255) NOT NULL COMMENT '考试项目名称',
        exam_date DATE DEFAULT NULL COMMENT '考试日期',
        subject_count INT DEFAULT 0 COMMENT '科目数量',
        students_per_subject INT DEFAULT 0 COMMENT '每科目学生数',
        rooms_per_subject INT DEFAULT 0 COMMENT '每科目考场数',
        invigilators_per_subject INT DEFAULT 2 COMMENT '每考场监考员数',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        status ENUM('draft', 'active', 'completed', 'cancelled') DEFAULT 'draft' COMMENT '状态',
        created_by VARCHAR(50) DEFAULT NULL COMMENT '创建人',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        description TEXT COMMENT '描述',
        end_date DATE DEFAULT NULL COMMENT '结束日期',
        progress INT DEFAULT 0 COMMENT '项目进度',

        INDEX idx_exam_name (exam_name),
        INDEX idx_exam_date (exam_date),
        INDEX idx_status (status),
        INDEX idx_created_by (created_by),
        INDEX idx_progress (progress)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='考试项目表'
    """

    try:
        cursor.execute(sql)
        print("✅ 考试项目表(exams)创建成功")
        return True
    except Exception as e:
        print(f"❌ 考试项目表创建失败: {e}")
        return False


def create_arrange_examination_rooms_and_invigilators_table(cursor):
    """创建考场编排表（适用于"编排考场及预编排监考员"页面）"""
    sql = """
    CREATE TABLE IF NOT EXISTS arrange_examination_rooms_and_invigilators (
        id INT AUTO_INCREMENT PRIMARY KEY,
        exam_id INT NOT NULL COMMENT '考试项目ID',
        session_number INT NOT NULL COMMENT '考试场次',
        
        -- 基本考场信息
        subject_name VARCHAR(100) DEFAULT NULL COMMENT '本场次科目名称',
        exam_date DATE DEFAULT NULL COMMENT '本场次考试日期',
        start_time TIME DEFAULT NULL COMMENT '本场次考试开始时间',
        end_time TIME DEFAULT NULL COMMENT '本场次考试结束时间',
        room_type VARCHAR(50) DEFAULT NULL COMMENT '本场次使用考场类型（学生课室/实训室/电脑机房）',
        total_rooms_no_backup INT DEFAULT 0 COMMENT '本场次需要的考场总数（不含备用考场）',
        room_numbering_pattern VARCHAR(50) DEFAULT NULL COMMENT '考场号编号规律',
        students_per_room INT DEFAULT 0 COMMENT '本场次每个考场的考生数量',
        invigilators_per_room INT DEFAULT 2 COMMENT '本考试项目每个考场需要监考员数量（2/3）',
        backup_rooms_needed INT DEFAULT 0 COMMENT '本项目需要的备用考场数量（0/1）',
        
        -- 计算结果字段
        total_rooms_with_backup INT DEFAULT 0 COMMENT '本场次需要的考场总数（含备用考场）',
        total_invigilators_needed INT DEFAULT 0 COMMENT '本场次需要监考员总数',
        
        -- 遴选和编排结果
        selected_classrooms_list TEXT COMMENT '本场次使用课室的课室号列表（含备用考场）JSON格式',
        exam_room_numbers_list TEXT COMMENT '本场次使用课室的考场号列表（含备用考场）JSON格式',
        invigilators_list TEXT COMMENT '本场次监考员的姓名列表（含备用考场）JSON格式',
        
        -- 详细映射关系（保留原有字段兼容性）
        room_assignments TEXT COMMENT '课室分配信息(JSON格式)',
        invigilator_assignments TEXT COMMENT '监考员分配信息(JSON格式)',
        classroom_exam_room_mapping TEXT COMMENT '课室号-考场号对应关系(JSON格式)',
        classroom_invigilator_mapping TEXT COMMENT '课室号-监考员对应关系(JSON格式)',
        
        -- 系统字段
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

        UNIQUE KEY uk_exam_session (exam_id, session_number),
        INDEX idx_exam_id (exam_id),
        INDEX idx_session_number (session_number),
        INDEX idx_exam_date (exam_date),
        INDEX idx_room_type (room_type),
        INDEX idx_subject_name (subject_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='考场编排表'
    """

    try:
        cursor.execute(sql)
        print("✅ 考场编排表(arrange_examination_rooms_and_invigilators)创建成功")
        return True
    except Exception as e:
        print(f"❌ 考场编排表创建失败: {e}")
        return False


def create_violations_table(cursor):
    """创建违规记录表（新增 - 支持违规管理系统）"""
    sql = """
    CREATE TABLE IF NOT EXISTS violations (
        id INT AUTO_INCREMENT PRIMARY KEY,
        exam_id INT NOT NULL COMMENT '考试项目ID',
        exam_name VARCHAR(255) NOT NULL COMMENT '考试项目名称',
        exam_session VARCHAR(100) DEFAULT NULL COMMENT '考试场次',
        invigilator_name VARCHAR(100) NOT NULL COMMENT '监考员姓名',
        invigilator_id VARCHAR(50) NOT NULL COMMENT '监考员工号',
        violation_date DATE NOT NULL COMMENT '违规日期',
        violation_type VARCHAR(100) NOT NULL COMMENT '违规类型',
        severity_level ENUM('轻微', '一般', '严重', '极严重') NOT NULL DEFAULT '一般' COMMENT '严重程度',
        description TEXT COMMENT '违规描述详情',
        disciplinary_violation_points DECIMAL(5,2) DEFAULT 0.00 COMMENT '违反纪律积分',
        reported_by VARCHAR(50) DEFAULT NULL COMMENT '举报人',
        status ENUM('待处理', '已处理', '已撤销') DEFAULT '待处理' COMMENT '处理状态',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

        INDEX idx_exam_id (exam_id),
        INDEX idx_invigilator_id (invigilator_id),
        INDEX idx_violation_date (violation_date),
        INDEX idx_severity_level (severity_level),
        INDEX idx_status (status),
        INDEX idx_exam_name (exam_name),
        INDEX idx_invigilator_name (invigilator_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='违规记录表'
    """

    try:
        cursor.execute(sql)
        print("✅ 违规记录表(violations)创建成功")
        return True
    except Exception as e:
        print(f"❌ 违规记录表创建失败: {e}")
        return False


def extract_building_number(building_text):
    """从楼栋文本中提取数字"""
    if not building_text:
        return None
    match = re.search(r'(\d+)', building_text)
    return int(match.group(1)) if match else None


def extract_floor_number(floor_text):
    """从楼层文本中提取数字"""
    if not floor_text:
        return None
    if '地下一楼' in floor_text:
        return -1
    match = re.search(r'(\d+)', floor_text)
    return int(match.group(1)) if match else None


def generate_address(building, floor, room_number):
    """生成课室详细地址"""
    if not building or not floor or not room_number:
        return None
    return f"{building}{floor}{room_number}"


def insert_critical_data(cursor):
    """插入关键数据 - 使用统一的examination_rooms表（增强版本）"""

    print("📊 开始插入关键数据（使用统一examination_rooms表 - 增强版本）...")

    # 插入用户指定的角色数据
    role_sql = """
    INSERT IGNORE INTO user_roles (user_id, role, assigned_by) VALUES
    ('1236', '系统管理员', 'system'),
    ('1236', '考试项目管理员', 'system'),
    ('1236', '人事管理员', 'system'),
    ('1236', '普通账号', 'system'),
    ('1003', '普通账号', 'system')
    """


    try:
        cursor.execute(role_sql)
        print("✅ 用户指定的角色数据插入成功")
    except Exception as e:
        print(f"❌ 用户角色数据插入失败: {e}")


def show_statistics(cursor):
    """显示数据库统计信息"""
    print("📈 数据库初始化完成统计:")

    tables = [
        ('staff', '员工信息'),
        ('user_roles', '用户角色'),
        ('login_attempts', '登录尝试'),
        ('blocked_ips', 'IP封禁'),
        ('examination_rooms', '统一课室信息'),
        ('exam_classrooms', '考试课室关联'),
        ('exams', '考试项目'),
        ('arrange_examination_rooms_and_invigilators', '考场编排'),
        ('violations', '违规记录')
    ]

    for table_name, table_desc in tables:
        try:
            cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
            count = cursor.fetchone()[0]
            print(f"   {table_desc}({table_name}): {count} 条记录")
        except Exception as e:
            print(f"   {table_desc}({table_name}): 查询失败 - {e}")


def main():
    """主函数"""
    print("🚀 开始初始化考试项目课室管理系统数据库...")
    print(f"⏰ 初始化时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
    print("🔄 表结构增强版本 - 支持课室管理功能和违规管理系统")
    print("-" * 80)

    # 获取数据库连接
    connection = get_db_connection()
    if not connection:
        print("❌ 数据库初始化失败：无法连接数据库")
        print("💡 请检查数据库配置信息")
        sys.exit(1)

    try:
        cursor = connection.cursor()

        # 删除旧的rooms表（如果存在）
        print("🗑️ 清理旧表结构...")
        try:
            cursor.execute("DROP TABLE IF EXISTS rooms")
            print("✅ 已删除旧的rooms表")
        except Exception as e:
            print(f"⚠️ 删除rooms表失败: {e}")

        # 创建表
        print("📋 开始创建数据库表...")

        table_functions = [
            ("员工信息表", create_staff_table),
            ("用户角色表", create_user_roles_table),
            ("登录尝试表", create_login_attempts_table),
            ("IP封禁表", create_blocked_ips_table),
            ("统一课室信息表", create_examination_rooms_table),
            ("考试课室关联表", create_exam_classrooms_table),
            ("考试项目表", create_exams_table),
            ("考场编排表", create_arrange_examination_rooms_and_invigilators_table),
            ("违规记录表", create_violations_table)
        ]

        success_count = 0
        for table_name, create_func in table_functions:
            if create_func(cursor):
                success_count += 1

        # 提交表创建
        connection.commit()
        print(f"📊 表创建完成: {success_count}/{len(table_functions)} 个表创建成功")
        print("-" * 60)

        # 插入关键数据
        insert_critical_data(cursor)

        # 提交数据插入
        connection.commit()
        print("-" * 60)

        # 显示统计信息
        show_statistics(cursor)


    except Exception as e:
        print(f"❌ 数据库初始化过程中发生错误: {e}")
        connection.rollback()
        sys.exit(1)

    finally:
        cursor.close()
        connection.close()
        print("🔒 数据库连接已关闭")


if __name__ == "__main__":
    main()